Imports System.Windows.Forms

Public Class dlgDailyMenu

    Dim dt As New DataTable
    Dim sqlStr As String

    Private Sub dlgDailyMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dtpDay.Value = Now()
        cbDish.DataSource = db.getvalue("Select DishID,DishName from Dish").Tables(0)
        cbDish.ValueMember = "DishID"
        cbDish.DisplayMember = "DishName"
        'sqlStr = "select DishName,Quantity from DailyMenu inner join Dish on DailyMenu.DishID = Dish.DishID where DateID = '" & dtpDay.Value.Date & "'"
        bindSQL()
    End Sub
    Private Sub bindSQL()
        sqlStr = "select DishName,Quantity from DailyMenu inner join Dish on DailyMenu.DishID = Dish.DishID where DateID = '" & dtpDay.Value.Date & "'"
        Dim bs As New BindingSource
        dt = db.getvalue(sqlStr).Tables(0)
        bs.DataSource = dt
        dgvDailyMenu.DataSource = bs

        dgvDailyMenu.Columns(1).ValueType = nupQuantity.GetType
        For i As Integer = 0 To dt.Columns.Count - 1
            dgvDailyMenu.Columns(i).Name = dgvDailyMenu.Columns(i).HeaderText
        Next
        dgvDailyMenu.Columns("Dishname").ReadOnly = True
        'dgvECoupon.Columns("Created by").ReadOnly = True
        'dgvECoupon.AllowUserToDeleteRows = False
        dgvDailyMenu.AllowUserToAddRows = False

    End Sub


    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim sql As String
        If cbDish.Text.Length > 0 Then
            Try
                sql = "insert into DailyMenu values('" & dtpDay.Value.Date & "','" & cbDish.SelectedValue & "'," & nupQuantity.Value & ")"
                db.update(sql)
                bindSQL()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If
    End Sub


    Private Sub dtpDay_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpDay.ValueChanged
        bindSQL()
    End Sub

    Private Sub dgvDailyMenu_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvDailyMenu.CellEndEdit
        Try
            db.update("update dailymenu set Quantity=" & dgvDailyMenu.Item(e.ColumnIndex, e.RowIndex).Value & " where DishID = (select DishID from Dish where DishName ='" & dgvDailyMenu.Rows(e.RowIndex).Cells("DishName").Value & "') and DateID='" & dtpDay.Value.Date & "'")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub dgvDailyMenu_UserDeletingRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowCancelEventArgs) Handles dgvDailyMenu.UserDeletingRow
        If MsgBox("Do you want to delete '" & e.Row.Cells(0).Value & "'?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            Try
                db.update("delete from dailymenu where DishID = (select DishID from Dish where DishName ='" & e.Row.Cells(0).Value & "') and DateID='" & dtpDay.Value.Date & "'")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        Else
            e.Cancel = True
        End If
    End Sub
    Public Function DateToNumber(ByVal dte As Date) As Integer
        Return dte.Year & FillSpace(dte.Month, 2, 0, "0", "Left") & FillSpace(dte.Day, 2, 0, "0", "Left")
    End Function
    Public Function NumberToDate(ByVal number As Integer) As Date
        Return Mid$(number, 1, 4) & Mid$(number, 5, 2) & Mid$(number, 7, 2)
    End Function

End Class
